# SQLAlchemy 操作数据库

from sqlalchemy import Column, String, create_engine, Integer, Text, Enum, Float
from sqlalchemy import DECIMAL, Boolean, Date, DateTime, Time
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
# 连接数据库

 # 初始化数据库连接: '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
# 注意 python3 只支持 pymysql
engine = create_engine('mysql+pymysql://root:admin123@localhost:3306/runoob')
db_session = sessionmaker(bind=engine)


# 定义表
class User(Base):
    # 表名
    __tablename__ = "users"
    # 创建表结构 支持的数据类型参见 md 文件
    id = Column(Integer, primary_key=True, autoincrement=True)
    number = Column(Text, nullable=False)
    name = Column(String(20), nullable=False)
    sex = Column(Enum("man", "female"))
    stature = Column(Float)
    # DECIMAL有两个参数,第一个参数用于指定一共多少位数，第二个参数用于指定小数点后最多多少位数
    weight = Column(DECIMAL(10, 4))
    flag = Column(Boolean)
    # 指定格式
    birthday = Column(DateTime)
    myData = Column(Date)
    myTime = Column(Time)
    remark = Column(LONGTEXT)

    def __repr__(self):
        return '%s(%d %s %r %s)' % (self.__class__.__name__, self.id, self.number, self.name, self.sex)

# 创建数据库
def create_table():
    Base.metadata.create_all(engine)


#  新增数据
def add_data():
    for i in range(0, 10):
        user = User()
        user.number = "10"+str(i)
        user.name = "name"+str(i)
        user.sex = "man"
        user.flag = True
        session = db_session()
        result = session.add(user)
        print("add result = ", result)
        session.commit()
    session.close()

# 查询数据
def query_data():
    session = db_session()
    user_list = session.query(User.id, User.number, User.name, User.sex).all()

    for user in user_list:
        print(user)
# 删除数据
def delete_data():
    session = db_session()
    session.query(User).filter(User.number == "100").delete()
    session.commit()
    session.close()
# 修改数据
def update_data():
    session = db_session()
    session.query(User).filter(User.name == "name1").update({User.name: "xxxx"})
    session.commit()

if __name__ == '__main__':
    create_table()
    #add_data()
    #delete_data()
    update_data()
    query_data()
